﻿using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using Microsoft.SharePoint;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DemoOpenXMLExcel
{
    class Program
    {
        private const string url = "http://spvm/sites/demoopenxml";
        static void Main(string[] args)
        {
            Console.WriteLine("/*Génération Document EXCEL Sample*/\n");
            GenerateDocument();
            Console.WriteLine("/*Fin*/\n");
            Console.ReadKey();
        }

        private static void GenerateDocument()
        {
            Stream docstream = null;
            using (SPSite site = new SPSite(url))
            {
                SPWeb web = site.OpenWeb();
                SPFile spf = web.GetFile("Shared Documents/EXCEL_TEMPLATE.xlsx");
                if (!spf.Exists)
                {
                    Console.WriteLine("Le Document n'éxiste pas\n");
                }
                else
                {
                    docstream = spf.OpenBinaryStream();
                }
                if (docstream != null)
                {
                    Console.WriteLine("Début de la phase de ExcelProcessing\n");

                    using (SpreadsheetDocument outDoc = SpreadsheetDocument.Open(docstream, true))
                    {
                        WorkbookPart wbPart = outDoc.WorkbookPart;
                        Sheet tabSheet = wbPart.Workbook.Descendants<Sheet>().
                            Where(s => s.Name != null && s.Name.Value.ToString().Trim() == "Demo").FirstOrDefault();

                        if (tabSheet != null)
                        {
                            Worksheet wsheet = ((WorksheetPart)(wbPart.GetPartById(tabSheet.Id))).Worksheet;
                            SheetData sheetData = wsheet.GetFirstChild<SheetData>();

                            SPList spList = web.Lists.TryGetList("demo");
                            if (spList != null)
                            {
                                SPQuery qry = new SPQuery();
                                qry.ViewFields = @"<FieldRef Name='Description' /><FieldRef Name='Title' />";
                                SPListItemCollection listitems = spList.GetItems(qry);

                                uint startline = 6;
                               
                                foreach (SPListItem item in listitems)
                                {
                                    
                                    Helper.InsertRow(wsheet);//Insert after the last row
                                    Cell cellA = Helper.CreateCell(item["Title"].ToString(), "A", startline , wsheet);
                                    Cell cellB = Helper.CreateCell(item["Description"].ToString(), "B", startline, wsheet);
                                    startline++;
                                }
                                

                            }
                            //Update a Simple Cell
                            Helper.UpdateCell(wsheet, "New Value in Cell", 3, "A");
                            wsheet.Save();

                        }
                    }

                   
                    CREATE_OUTPUT_DOC(web, docstream, "OUT_EXCEL_TEMPLATE", ".xlsx");
                }
            }
        }



        
        private static void CREATE_OUTPUT_DOC(SPWeb web, Stream outputStream, string filename, string ext)
        {
            try
            {
                SPList outputLibrary = web.Lists.TryGetList("Documents");
                if (outputLibrary != null)
                {
                    string outputFileUrl = string.Format("{0}/{1}", outputLibrary.RootFolder.ServerRelativeUrl, filename + ext);
                    web.AllowUnsafeUpdates = true;
                    outputLibrary.RootFolder.Files.Add(outputFileUrl, outputStream);
                    web.Update();
                    if (outputStream != null)
                    {
                        outputStream.Dispose();
                    }
                    Console.WriteLine("Le Document a bien été généré\n");
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Erreur de création de Document\n :" + ex.ToString());
            }
        }

    }
}
